﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Automobile.service.Common;
using Automobile.service.Model;

namespace Automobile.service.DAL
{
    public class StatisticsShipmentsDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 查询年份
        /// </summary>
        /// <param name="TimeStart"></param>
        /// <param name="TimeEnd"></param>
        /// <returns></returns>
        public List<SearchYear> SearchY(string TimeStart,string TimeEnd)
        {
            string sql = "select YEAR(Shipments_data) as 年,SUM(Shipments_Price) as 总金额,sum((Product_Price-Product_Bprid)*ShipmentMate_Num) as 盈利 from Product p join ShipmentMate s on p.Product_Id=s.Product_Id join Shipments ss on s.Shipments_Id=ss.Shipments_Id where year(Shipments_data) between '" + TimeStart + "' and '" + TimeEnd + "' and Shipments_State=1 group by YEAR(Shipments_data) ";
            List<SearchYear> list = new List<SearchYear>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                SearchYear p = new SearchYear();
                p.YearNum = item["年"].ToString();
                p.YearMoney = item["总金额"].ToString();
                p.ProfitY = item["盈利"].ToString();
                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 查询月份
        /// </summary>
        /// <param name="SYear"></param>
        /// <param name="TimeStart"></param>
        /// <param name="TimeEnd"></param>
        /// <returns></returns>
        public List<SearchMonth> SearchM(string SYear,string TimeStart,string TimeEnd)
        {
            string sql = "select Month(Shipments_data) as 月,SUM(Shipments_Price) as 总金额,sum((Product_Price-Product_Bprid)*ShipmentMate_Num) as 盈利 from Product p join ShipmentMate s on p.Product_Id=s.Product_Id join Shipments ss on s.Shipments_Id=ss.Shipments_Id where year(Shipments_data) ='" + SYear + "' and MONTH(Shipments_data) between '" + TimeStart + "' and '" + TimeEnd + "' and Shipments_State=1 group by Month(Shipments_data)";
            List<SearchMonth> list = new List<SearchMonth>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                SearchMonth p = new SearchMonth();
                p.MonthNum = item["月"].ToString();
                p.MonthMoney = item["总金额"].ToString();
                p.Profit = item["盈利"].ToString();
                list.Add(p);
            }
            return list;
        }
    }
}
